import requests #爬取网页
import json
import xlwings as xw
url = 'https://c.m.163.com/ug/api/wuhan/app/data/list-total?t=329822670771'
headers = {'user-agent' : 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/97.0.4692.71 Safari/537.36 Edg/97.0.1072.55'}
response = requests.get(url , headers = headers)

wb = xw.Book() #相当于打开excel操作
sht = wb.sheets('sheet1')

json_data = response.json()['data']['areaTree']
for i in range(206):
    earth_data = json_data[i]
    name = earth_data['name']
    sht.range(f'A{i+2}').value = name
    today_confirm = json.dumps(earth_data['today']['confirm'])
    sht.range(f'B{i+2}').value = today_confirm
    total_confirm = json.dumps(earth_data['total']['confirm'])
    sht.range(f'C{i+2}').value = total_confirm
    total_dead = json.dumps(earth_data['total']['dead'])
    sht.range(f'D{i+2}').value = total_dead
    total_heal = json.dumps(earth_data['total']['heal'])
    sht.range(f'E{i+2}').value = total_heal
    date = earth_data['lastUpdateTime']
    sht.range(f'F{i+2}').value = date
wb.save(r'E:\bysj\diango\exc\世界疫情数据爬取.xlsx')
wb.close()
